﻿create PROCEDURE Sync.SP_Images_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @Images_Temp TABLE 
		(
			ImageID UniqueIdentifier,
			[ImageData] varbinary,
				[ImageTypeID] uniqueidentifier,
				[Label] nvarchar(250),
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @Images_Temp 
	   SELECT	Tbl.Col.value('ImageID[1]','uniqueidentifier') ImageID,
                Tbl.Col.value('ImageData[1]', 'varbinary') [ImageData],
Tbl.Col.value('ImageTypeID[1]', 'uniqueidentifier') [ImageTypeID],
Tbl.Col.value('Label[1]', 'nvarchar(250)') [Label],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/Images') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[Images] ([ImageID],[ImageData],[ImageTypeID],[Label])
	SELECT t.[ImageID],t.[ImageData],t.[ImageTypeID],t.[Label]
	FROM  @Images_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.Images_Tracking s
        WHERE t.ImageID = s.ImageID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[Images]
	SET [Images].[ImageData] = t.[ImageData],[Images].[ImageTypeID] = t.[ImageTypeID],[Images].[Label] = t.[Label] 
	FROM [dbo].[Images] s JOIN @Images_Temp t 
    ON t.ImageID = s.ImageID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.Images_Tracking r
				  WHERE t.ImageID = r.ImageID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[Images]
    FROM @Images_Temp t JOIN [dbo].[Images] s
    ON t.ImageID = s.ImageID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END














